前言

在前一章節中，我們列出了ClickHouse資料庫與資料表引擎，在本章節中，要來討論ClickHouse資料庫裡面的SQL形式。

SQL敘述

在ClickHouse資料庫中，提供了許多SQL敘述供我們使用，下列呈現的是所有SQL敘述的使用方法。

SELECT

SELECT語句執行是用來取得資料，預設來說，查詢完之後，請求的資料結果會回傳到客戶端的終端機上，相關的語法如下所示：

[WITH expr_list|(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [COMPRESSION type [LEVEL level]] ]
[FORMAT format]

所有的從句(clauses)是可選的，如果有接上某個從句的話，則後面的清單才會是必填的。

每個可選的從句在SQL語句中是可以在分開的段落，相關的從句如下所示：

WITH Clause

ClickHouse資料庫支援常見的資料表描述Common Table Expressions (CTE)，這可以用在查詢的結果或是命名查詢子句，下列這兩種語法皆可以，相關的語法如下：

WITH <expression> AS <identifier>
WITH <identifier> AS <subquery expression>

相關的範例如下：

範例1、使用將定值描述成某一個變數：

WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound;

範例2、將一個sum(bytes)描述結果包含在SELECT語句的欄位清單中：

WITH sum(bytes) as s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;

範例3、使用純量scalar子查詢當作結果：

/* 這個範例將會回傳前十個資料量最大的資料表 */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

範例4、在子查詢中重複使用描述

WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;

SELECT Clause

在SELECT從句中 specified in the SELECT clause are calculated after all the operations in the clauses described above are finished. These expressions work as if they apply to separate rows in the result. If expressions in the SELECT clause contain aggregate functions, then ClickHouse processes aggregate functions and expressions used as their arguments during the GROUP BY aggregation.

如果我們在查詢結果中，需要包含資料表中所有欄位，可以使用asterisk (*)符號。舉例來說，可已使用SELECT * FROM .....的查詢語句。

DISTINCT Clause

如果使用SELECT DISTINCT語句的話，只有獨特的資料行數才會保留在查詢的結果中，因此在結果中有筆資料符合，則只有單一一筆資料出現在查詢結果中。

我們可以指定欄位的清單，且必須指定欄位，例如：SELECT DISTINCT ON (column1, column2,...)。如果沒有指定欄位的話，則所有的欄位都會考慮。

我們考慮下列的資料表內容：

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

則使用DISTINCT的從句並沒有指定欄位之後，相關的SQL語句如下：

SELECT DISTINCT * FROM t1;

則執行上述的SQL語句之後，會得到下列的查詢結果：

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

若使用DISTINCT的從句並搭配指定的欄位的話，相關的SQL語句如下：

SELECT DISTINCT ON (a, b) * FROM t1;

則執行上述的SQL語句之後，會得到下列的查詢結果：

┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘

ClickHouse資料庫支援在一個查詢語句中，可以針對資料表中不同的欄位同時使用DISTINCT與ORDER BY從句。而DISTINCT從句會先執行，接著才會執行ORDER BY從句。

我們以下列的資料表當作範例：

┌─a─┬─b─┐
│ 2 │ 1 │
│ 1 │ 2 │
│ 3 │ 3 │
│ 2 │ 4 │
└───┴───┘

接著使用下列的方式進行查詢資料，這個SQL查詢將會讓查詢結果利用b欄位進行由小到大的排列：

SELECT DISTINCT a FROM t1 ORDER BY b ASC;

執行上述的查詢語法之後，則會得到如下的結果：

┌─a─┐
│ 2 │
│ 1 │
│ 3 │
└───┘

使用下列的查詢語句將查詢結果以b欄位由大到小方式呈現：

SELECT DISTINCT a FROM t1 ORDER BY b DESC;

執行上述的查詢結果如下：

┌─a─┐
│ 3 │
│ 1 │
│ 2 │
└───┘

我們從上述兩個SQL查詢語句得知，資料表中的a欄位是2且b欄位是4的筆數會在排序之前先被去掉。

如果指定的欄位中有空值，DISTINCT從句可以處理空值NULL的情形。即會用NULL==NULL來判斷是否是重複的資料。換句話說，在DISTINCT從句中查詢的結果，不同的空值NULL只會出現一次。

我們可以透過在同一個SELECT子句中使用GROUP BY從句來來獲得相同的結果，而且不需要使用任何聚合函數。但DISTINCT從句仍然與GROUP BY方法有些許的差別：

1. DISTINCT從句可以與GROUP BY從句可以一同出現在SQL語句中。
2. 當省略ORDER BY從句並定義LIMIT時，SQL查詢語句會在讀取所需要的數量不同行後立即停止執行。
3. 當資料區塊在處理輸出的時候，不需要等待整個查詢完成執行。

FROM Clause

FROM從句可以指定從哪個來源進行讀取資料的動作：

1. 從資料表Table
2. 從子查詢Subquery
3. 從資料表函式Table function

其中，資料表函式Table functions是用來建構話資料表的方法，我們可以將資料表函式用在如下的地方：

1. 在SELECT語句中的FROM從句中。
   • 建立暫時的資料表且只供在確切的查詢中使用，當查詢結束之後，這個資料表也降會被刪除。
2. 使用CREATE TABLE AS table_function()之查詢語句。
   • 是建立資料表的方法之一。
3. 在INSERT INTO TABLE FUNCTION之語句中使用。

需要注意的是，當allow_ddl設定已經關閉的話，則我們無法使用資料表函式，相關的資料表函式如下：

1. file，用來建立以檔案為引擎的資料表。
2. merge，用來建立Merge為引擎的資料表。
3. numbers，用來建立資料表只含有整數數值的單一欄位。
4. remote，可以允許我們存取遠端的伺服器且不需要建立分散式的Distributed引擎資料表。
5. url，建立Url引擎資料表。
6. mysql，建立MySQL引擎的資料表。
7. postgresql，建立PostgreSQL引擎的資料表。
8. jdbc，建立JDBC引擎的資料表。
9. odbc，建立ODBC引擎資料表。
10. hdfs，建立HDFS引擎資料表。
11. s3，建立Amazon S3引擎的資料表。
12. sqlite，建立SQLite引擎的資料表。

當FINAL在SQL語句中指定的話，ClickHouse會在回傳結果之前完整的合併資料，並執行給定的資料表引擎合併期間所發生的所有資料轉換。

這個是適用於當使用MergeTree資料庫引擎系列的資料表中選擇資料時使用，也支援下列的情形：

1. 資料複製(replicated data)版本的MergeTree資料庫引擎。
2. 視圖View、緩衝Buffer、分散式Distributed以及MaterializedView引擎，或是在其他的資料表引擎上執行，但是前提是這些引擎是在MergeTree資料表引擎表上面建立的。
3. 使用SELECT查詢並搭配FINAL語句執行時，會以併行且較為快速的方式執行語句。但是有一些缺點，相關的缺點如下所示，max_final_threads之設定會限制使用CPU執行緒的數量。

使用SQL查詢時使用FINAL語句的速度會比不使用FINAL語句的SQL查詢稍慢，相關的原因如下：

• 資料在執行查詢的時候，資料會以合併的方式進行。
• SQL查詢Queries建立時使用了FINAL時，除了讀取在查詢語句中指定的欄位之外，還會讀取設定主鍵的欄位。
• 在大多數的案例中，ClickHouse官方建應該要避免使用FINAL語句。
  • 常見的方法是使用不同的查詢。假設MergeTree資料表引擎的背景程序還沒有發生的時候，應透過使用聚合(aggregate)方式來處理它（例如，利用丟棄重複資料的方式(discard duplicates)）。

如果FROM從句是忽略的，資料將會從system.one資料表中進行查詢語讀取。這個system.one資料表包含了確切的筆數，而這個資料表與其他的資料庫系統DBMS中的DUAL資料表具有相同的目的。

為了要執行查詢，所有的欄位會列在查詢中，並從某個指定的資料表中進行讀取，外部查詢不需要任何的欄位，而且都將會從子查詢中丟棄。如果SQL查詢語句中沒有列出任何的欄位，像是SELECT count() FROM table的語句，不管怎樣都會從資料表中提取某些筆數（首要選則的是最小的筆數）來計算行數。

SAMPLE clause

SAMPLE語句允許在SELECT查詢結果，進行取樣的處理。

當資料取樣在SQL查詢中設定啟用，查詢語句將不會對所有的資料進行處理，即不是對所有資料執行查詢，而是僅對一部分資料（樣本）執行查詢。

舉例來說，如果我們需要計算所有有關於訪問網站的統計資訊，只需要針對所有訪問的統計資訊的1/10部分進行取樣並執行查詢，接著將結果乘以10即可。

近似的查詢處理是在下列的案例中是實用的：

• 當我們有嚴格的延遲需求（例如低於100毫秒）的查詢時間，但我們沒有額外硬體資源上述的要求。
• 當我們的原始資料不准確時，近似值不會明顯的降低數值品質。
• 業務的需求，需要有近似值的取樣結果報告，像是成本最佳化或是為了市場需求向高級使用者推銷較為準確的結果。

需要注意的是，我們只能在MergeTree系列的資料表引擎中進行取樣的查詢，且並且只有在建立資料表的時候定了採樣的方式。

資料取樣的特性清單如下：

• 資料取樣有決定性的機制，SELECT .. SAMPLE的查詢出來結果都會是相同的。
• 資料取樣在不同資料表都會一致性的取樣方式。對於一個資料表有單一的取樣鍵，一個樣本使用同一個係數，並時常選擇可能的當作子集。舉例來說，例如，用戶ID樣本會從不同的資料表中取得所有可能用戶ID有相同子集的筆數資料。這也意味著我們可以在IN從句中的子查詢中使用樣本。 此外，我們也可以使用JOIN從句連接樣本sample。
• 取樣樣本允許從硬碟中讀取少量的資料，我們要注意的是需要確切地指定樣本鍵，更多的資訊，可以參考有關於建立MergeTree資料表引擎的章節。

SAMPLE從句支援下列的語法：

1. SAMPLE k，k可以是0到1的數字，會以此取樣資料後，執行查詢語句，舉例來說設定SAMPLE 0.1會在10%的資料中執行SQL語句。
2. SAMPLE n，n可以是一個大的整數，會至少在這個筆數中執行SQL查詢(但遠不止於此)。舉例來說，SAMPLE 10000000會在最小有10,000,000筆數中執行SQL語句。
3. SAMPLE k OFFSET m，k與m可以是從0到1的數字，會以此取樣資料後，執行查詢語句，並用於樣本的資料偏移了多少m。

SAMPLE K

k可以是0到1的數字，用分數或是浮點數都是可以的。舉例來說，SAMPLE 1/2或是SAMPLE 0.5。

在SAMPLE k的語句來說，取樣是取資料裡面的百分比比例，相關的範例如下：

SELECT
    Title,
    count() * 10 AS PageViews
FROM hits_distributed
SAMPLE 0.1
WHERE
    CounterID = 34
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

在上述的範例中，在10%的資料執行查詢，聚合函數的值不會自動的更正，因此要獲得近似的取樣結果，需要手動將count()之值乘以10。

SAMPLE N

n充份的表示一個大整數，舉例來說，可以是SAMPLE 10000000。

在這種案例下，查詢需要是在至少n筆資料的樣本上執行的查詢。例如，SAMPLE 10000000對至少10,000,000筆數進行查詢。

由於讀取資料的最小單位是一個顆粒（其大小由資料庫設定中的index_granularity設定），因此設定一個遠大於顆粒大小數值的樣本數是有意義的。

當使用了SAMPLE n從句，我們不會知道那些有關於相對百分比的資料，所以我們不用知道聚合函數應該乘以的係數，可以使用 _sample_factor 虛擬欄位來獲得近似結果。

_sample_factor欄位包含了動態計算的相對係數，當我們使用了指定的採樣鍵來建立資料表時，就會自動建立此欄位。 _sample_factor欄位使用範例如下所示。

我們可以參考下列的訪問資料表，其中包含了有關於網站訪問流量的統計資訊，下面第一個範例是表示如何計算網頁頁面瀏覽量：

SELECT sum(PageViews * _sample_factor)
FROM visits
SAMPLE 10000000

下一個範例表示如何計算總瀏覽量：

SELECT sum(_sample_factor)
FROM visits
SAMPLE 10000000

這個範例表示如何計算平均連線在網站停留時間，我們注意到的是，我們不需要使用相關的係數來計算該欄位的平均值，相關的範例如下所示：

SELECT avg(Duration)
FROM visits
SAMPLE 10000000

SAMPLE K OFFSET M

範例1、k和m可以是從0到1的數值，相關的使用範例如下：

SAMPLE 1/10

從上述的範例來看，會從所有資料中取1/10的資料當作樣本，我們可以用下列的方式來表示這一情形，表示在14筆資料中取前面的1/10筆的資料當作樣本：

[++------------]

範例2如下：

SAMPLE 1/10 OFFSET 1/2

從上述的範例則取樣的示意如下：

[------++------]

從範例示意可以知道，首先會利用1/2取得14筆資料中的第7筆資料開始，取得1/10的資料當作樣本。

結論

在本章節中，介紹了一部分的從句，在下一章節中還會介紹其他的SELECT語句可以接的從句。

參考資料

• https://clickhouse.com/docs/en/sql-reference/statements/select